5  Update Data

5.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

  • om
  • ex

5.2 Table Operations

Create table

CREATE TABLE invoices_copy AS 
-- everything below can be just a standalong query, you can choose to create the new table with only certain data from the original 
SELECT *
FROM invoices

Locate the newly created table: invoices_copy in ap database:

SHOW TABLES
FROM ap
9 records
Tables_in_ap
general_ledger_accounts
invoice_archive
invoice_archive_copy
invoice_line_items
invoices
invoices_copy
terms
vendor_contacts
vendors

Delete Table

Always use full 3-part naming to be sure deleting the correct table

DROP TABLE ap.invoices_copy

Confirm deleted

SHOW TABLES
FROM ap
WHERE Tables_in_ap = 'invoices_copy'
0 records
Tables_in_ap

Recreate a Table

If you want to remove a table as long as the table exists in the database (Especially useful when you only want to create a temporary table):

DROP TABLE IF EXISTS invoices_copy

5.3 Data Operations

Create backup table for demonstration.

CREATE TABLE invoices_copy AS 
SELECT *
FROM invoices

See the schema for the new table. Easier for the adding new data later…

SELECT *
FROM invoices_copy
LIMIT 1
1 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
1 122 989319-457 2022-04-08 3813.33 3813.33 0 3 2022-05-08 2022-05-07

Create other backup tables:

DROP TABLE IF EXISTS invoice_archive_copy
CREATE TABLE invoice_archive_copy AS 
SELECT *
FROM invoice_archive

INSERT: Add New Records

Add 1 row to invoices_copy table. Notice that all column values must be entered, even if it is NULL.

INSERT INTO invoices_copy VALUES
(115, 97, '456789', '2022-08-01', 8344.50, 0, 0, 1, '2022-08-31', NULL)

Check the new record is in place:

SELECT *
FROM invoices_copy 
WHERE invoice_id = 115
1 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
115 97 456789 2022-08-01 8344.5 0 0 1 2022-08-31 NA

Add multiple records in one statement:

INSERT INTO invoices_copy VALUES
    (116, 97, '456701', '2022-08-02', 270.50, 0, 0, 1,
    '2022-09-01', NULL),
    (117, 97, '456791', '2022-08-03', 4390.00, 0, 0, 1,
    '2022-09-02', NULL),
    (118, 97, '456792', '2022-08-03', 565.60, 0, 0, 1,
    '2022-09-02', NULL)

Verify the new records have been added:

SELECT *
FROM ap.invoices_copy 
WHERE invoice_id > 115
3 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
116 97 456701 2022-08-02 270.5 0 0 1 2022-09-01 NA
117 97 456791 2022-08-03 4390.0 0 0 1 2022-09-02 NA
118 97 456792 2022-08-03 565.6 0 0 1 2022-09-02 NA

You can also copy and add a whole subset of records from one table (source table) to another (target table). To do this, you need to ensure:

  • Target table has all the columns in EXACTLY THE SAME names
  • Select only the same columns from the source table

Example: To copy a whole chunk of rows to invoice_arhchive.

To start with, the target table invoice_archive_copy has no records…

SELECT * 
FROM invoice_archive_copy
0 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date

Now, we add all invoices (source table) with 0 balance to the archive table.

INSERT INTO invoice_archive_copy
    (invoice_id, vendor_id, invoice_number,
    invoice_total, credit_total, payment_total,
    terms_id, invoice_date, invoice_due_date)
SELECT
    invoice_id, vendor_id, invoice_number,
    invoice_total, credit_total, payment_total,
    terms_id, invoice_date, invoice_due_date
FROM invoices_copy
WHERE invoice_total - payment_total - credit_total = 0

Now, let’s see the target table again…

SELECT * 
FROM invoice_archive_copy
LIMIT 10
10 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
1 122 989319-457 2022-04-08 3813.33 3813.33 0 3 2022-05-08 NA
2 123 263253241 2022-04-10 40.20 40.20 0 3 2022-05-10 NA
3 123 963253234 2022-04-13 138.75 138.75 0 3 2022-05-13 NA
4 123 2-000-2993 2022-04-16 144.70 144.70 0 3 2022-05-16 NA
5 123 963253251 2022-04-16 15.50 15.50 0 3 2022-05-16 NA
6 123 963253261 2022-04-16 42.75 42.75 0 3 2022-05-16 NA
7 123 963253237 2022-04-21 172.50 172.50 0 3 2022-05-21 NA
8 89 125520-1 2022-04-24 95.00 95.00 0 1 2022-05-04 NA
9 121 97/488 2022-04-24 601.95 601.95 0 3 2022-05-24 NA
10 123 263253250 2022-04-24 42.67 42.67 0 3 2022-05-24 NA

UPDATE: Modify Records

Example: We want to manually update an invoice record with a particular invoice_number

Now, let’s see what it looks like before any updates

SELECT *
FROM invoices_copy
WHERE invoice_number = '97/522'
1 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
78 121 97/522 2022-06-28 1962.13 1762.13 200 3 2022-07-28 2022-07-30

Now, we will update the payment_date and payment_total values of the same invoice:

UPDATE invoices_copy
SET payment_date = '2022-09-21', 
    payment_total = 19351.18
WHERE invoice_number = '97/522'

Now, let’s see what it looks like after the updates:

SELECT *
FROM invoices_copy
WHERE invoice_number = '97/522'
1 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
78 121 97/522 2022-06-28 1962.13 19351.18 200 3 2022-07-28 2022-09-21

We can also update the new values with the calculation from the old values.

Example: We want to increase the credit total by 100

UPDATE invoices_copy
SET credit_total = credit_total + 100 
/* mathmatically, this does not make sense, but here, it simply means credit_total (new) = credit_total (old) + 100 */
WHERE invoice_number = '97/522'

Now, let’s see what it looks like after the second update:

SELECT *
FROM invoices_copy
WHERE invoice_number = '97/522'
1 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
78 121 97/522 2022-06-28 1962.13 19351.18 300 3 2022-07-28 2022-09-21

You can also use update a batch of records meeting criteria from a subquery.

Example: we want to set all terms_id to 1 (instead of current 2) for a vendor with name called ‘Pacific Bell’. This is only possible when querying with vendor table.

Now, let’s see what all invoices look with vendor Pacific Bell like, before any updates:

SELECT *
FROM invoices_copy
WHERE vendor_id IN
      (SELECT vendor_id
       FROM vendors
       WHERE vendor_name = 'Pacific Bell')
6 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
13 95 111-92R-10096 2022-04-30 16.33 16.33 0 2 2022-05-20 2022-05-23
44 95 111-92R-10094 2022-06-01 19.67 19.67 0 2 2022-06-21 2022-06-24
49 95 111-92R-10097 2022-06-04 16.33 16.33 0 2 2022-06-24 2022-06-26
53 95 111-92R-10092 2022-06-09 46.21 46.21 0 2 2022-06-29 2022-07-02
83 95 111-92R-10093 2022-07-06 39.77 39.77 0 2 2022-07-26 2022-07-22
91 95 111-92R-10095 2022-07-15 32.70 32.70 0 2 2022-08-04 2022-08-06

Now, we apply the updates:

UPDATE invoices_copy
SET terms_id = 1
WHERE vendor_id =
      (SELECT vendor_id
       FROM vendors
       WHERE vendor_name = 'Pacific Bell')

Now, let’s see what all invoices look with vendor Pacific Bell like, after updates:

SELECT *
FROM invoices_copy
WHERE vendor_id IN
      (SELECT vendor_id
       FROM vendors
       WHERE vendor_name = 'Pacific Bell')
6 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
13 95 111-92R-10096 2022-04-30 16.33 16.33 0 1 2022-05-20 2022-05-23
44 95 111-92R-10094 2022-06-01 19.67 19.67 0 1 2022-06-21 2022-06-24
49 95 111-92R-10097 2022-06-04 16.33 16.33 0 1 2022-06-24 2022-06-26
53 95 111-92R-10092 2022-06-09 46.21 46.21 0 1 2022-06-29 2022-07-02
83 95 111-92R-10093 2022-07-06 39.77 39.77 0 1 2022-07-26 2022-07-22
91 95 111-92R-10095 2022-07-15 32.70 32.70 0 1 2022-08-04 2022-08-06

NOTE: it is a good idea to query and test the criteria (WHERE clause), before you apply any updates. The same goes to next section “DELETE”, when removing records.

DELETE: Remove Records

Example: we want to remove all the last 3 inserted records: vendor_id: 116, 117, 118

DELETE FROM invoices_copy
WHERE invoice_id >= 116

Now, let’s see the result: (all records from 116 and above are gone)

SELECT *
FROM invoices_copy
WHERE invoice_id >= 115
1 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
115 97 456789 2022-08-01 8344.5 0 0 1 2022-08-31 NA